﻿namespace Utils.DatabaseUtils
{
    using MySql.Data.MySqlClient;
    using System;
    using System.Data;
    using System.Xml;

    public class MySQLGetData
    {
        private MySqlConnection Conn;
        private MySqlTransaction Tran;

        public MySQLGetData()
        {
            this.Conn = MySQLConnect.GetConnection();
        }

        public MySQLGetData(string str_key)
        {
            this.Conn = MySQLConnect.GetConnection(str_key);
        }

        public DataSet GetDataSet(string strProcedure, string[] arrParaNames, string[] arrValues)
        {
            DataSet CS10000;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand {
                    CommandTimeout = 180,
                    Connection = this.Conn,
                    CommandType = CommandType.StoredProcedure,
                    CommandText = strProcedure
                };
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                using (MySqlDataAdapter adap = new MySqlDataAdapter())
                {
                    adap.SelectCommand = Cmd;
                    using (DataSet result = new DataSet())
                    {
                        adap.Fill(result);
                        CS10000 = result;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }

        public DataSet GetDataSet(string strProcedure, string[] arrParaNames, string[] arrValues, int Startrecord, int Maxrecord)
        {
            DataSet CS10000;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand {
                    CommandTimeout = 180,
                    Connection = this.Conn,
                    CommandType = CommandType.StoredProcedure,
                    CommandText = strProcedure
                };
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                using (MySqlDataAdapter adap = new MySqlDataAdapter())
                {
                    adap.SelectCommand = Cmd;
                    using (DataSet result = new DataSet())
                    {
                        adap.Fill(result, Startrecord, Maxrecord, "tbresult");
                        CS10000 = result;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }

        public DataTable GetDataTable(string str_Query)
        {
            DataTable CS10000;
            try
            {
                MySqlCommand Cmd = new MySqlCommand();
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.Text;
                Cmd.CommandText = str_Query;
                using (MySqlDataAdapter adap = new MySqlDataAdapter())
                {
                    adap.SelectCommand = Cmd;
                    using (DataTable result = new DataTable())
                    {
                        adap.Fill(result);
                        CS10000 = result;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
            }
            return CS10000;
        }

        public DataTable GetDataTable(string strProcedure, string[] arrParaNames, string[] arrValues)
        {
            DataTable CS10000;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand();
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                using (MySqlDataAdapter adap = new MySqlDataAdapter())
                {
                    adap.SelectCommand = Cmd;
                    using (DataTable result = new DataTable())
                    {
                        adap.Fill(result);
                        CS10000 = result;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
            }
            return CS10000;
        }

        public DataTable GetDataTable(string strProcedure, string[] arrParaNames, object[] arrValues, MySqlDbType[] arrType)
        {
            DataTable CS10000;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand();
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.Add("@p" + arrParaNames[i], arrType[i]).Value = arrValues[i];
                }
                using (MySqlDataAdapter adap = new MySqlDataAdapter())
                {
                    adap.SelectCommand = Cmd;
                    using (DataTable result = new DataTable())
                    {
                        adap.Fill(result);
                        CS10000 = result;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
            }
            return CS10000;
        }

        public DataTable GetDataTable(string strProcedure, string[] arrParaNames, string[] arrValues, string[] arrParaNameOutput, ref string[] arrValueOutput)
        {
            DataTable CS10000;
            if ((arrParaNames.Length != arrValues.Length) || (arrParaNameOutput.Length != arrValueOutput.Length))
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            DataTable result = new DataTable();
            try
            {
                int j;
                MySqlCommand Cmd = new MySqlCommand {
                    CommandTimeout = 180
                };
                this.Conn.Open();
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                MySqlParameter[] p = new MySqlParameter[arrParaNameOutput.Length];
                for (j = 0; j < arrParaNameOutput.Length; j++)
                {
                    p[j] = new MySqlParameter("@p" + arrParaNameOutput[j], arrValueOutput[j]);
                    p[j].Direction = ParameterDirection.InputOutput;
                    Cmd.Parameters.Add(p[j]);
                }
                using (MySqlDataAdapter adap = new MySqlDataAdapter())
                {
                    adap.SelectCommand = Cmd;
                    adap.Fill(result);
                }
                for (j = 0; j < arrParaNameOutput.Length; j++)
                {
                    arrValueOutput[j] = p[j].Value.ToString();
                }
                CS10000 = result;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }

        public DataTable GetDataTableByQuery(string str_Query)
        {
            DataTable CS10000;
            try
            {
                MySqlCommand Cmd = new MySqlCommand();
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
                Cmd.CommandTimeout = 180;
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.Text;
                Cmd.CommandText = str_Query;
                using (MySqlDataAdapter adap = new MySqlDataAdapter())
                {
                    adap.SelectCommand = Cmd;
                    using (DataTable result = new DataTable())
                    {
                        adap.Fill(result);
                        CS10000 = result;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
            }
            return CS10000;
        }

        public object GetExecuteScalar(string str_Query)
        {
            object CS10000;
            try
            {
                MySqlCommand Cmd = new MySqlCommand();
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
                Cmd.CommandTimeout = 180;
                this.Conn.Open();
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.Text;
                Cmd.CommandText = str_Query;
                CS10000 = Cmd.ExecuteScalar();
            }
            catch (MySqlException ex)
            {
                throw ex;
            }
            finally
            {
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
            }
            return CS10000;
        }

        public object GetExecuteScalar(string strProcedure, string[] arrParaNames, string[] arrValues)
        {
            object CS10000;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand();
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
                Cmd.CommandTimeout = 180;
                this.Conn.Open();
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                CS10000 = Cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }

        public object GetExecuteScalar(string strProcedure, string[] arrParaNames, object[] arrValues, MySqlDbType[] arrType)
        {
            object CS10000;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand();
                if (this.Conn.State != ConnectionState.Closed)
                {
                    this.Conn.Close();
                }
                Cmd.CommandTimeout = 180;
                this.Conn.Open();
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.Add("@p" + arrParaNames[i], arrType[i]).Value = arrValues[i];
                }
                CS10000 = Cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return CS10000;
        }

        public DataSet GetXMLData(string FileName)
        {
            DataSet CS10000;
            try
            {
                XmlTextReader mReader = new XmlTextReader(FileName);
                DataSet mDataSet = new DataSet();
                mDataSet.ReadXml(mReader);
                mReader.Close();
                CS10000 = mDataSet;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return CS10000;
        }

        public string Returnvalue(string strProcedure, string[] arrParaNames, string[] arrValues)
        {
            string result;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand {
                    CommandTimeout = 180
                };
                this.Conn.Open();
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                result = Cmd.ExecuteScalar().ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return result;
        }

        public int Returnvalue(string strProcedure, string[] arrParaNames, string[] arrValues, ref string strResult)
        {
            int result = -1;
            if (arrParaNames.Length != arrValues.Length)
            {
                throw new ArgumentException("The Array Parameter Names and Array Parameter Values is not equal", "arrParaNames or arrValues");
            }
            try
            {
                MySqlCommand Cmd = new MySqlCommand {
                    CommandTimeout = 180
                };
                this.Conn.Open();
                Cmd.Connection = this.Conn;
                Cmd.CommandType = CommandType.StoredProcedure;
                Cmd.CommandText = strProcedure;
                for (int i = 0; i < arrParaNames.Length; i++)
                {
                    Cmd.Parameters.AddWithValue("@p" + arrParaNames[i], arrValues[i]);
                }
                MySqlParameter p = Cmd.Parameters.Add("@p" + strResult, SqlDbType.Int);
                p.Direction = ParameterDirection.Output;
                result = Cmd.ExecuteNonQuery();
                strResult = Convert.ToString(p.Value);
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                this.Conn.Close();
            }
            return result;
        }
    }
}

